using Dapper;
using Lzfy_Refund_Service.Models;
using Lzfy_Refund_Service.Services;
using System.Data;

namespace Lzfy_Refund_Service.Repositories
{
    /// <summary>
    /// 退款审核工作记录仓储实现
    /// </summary>
    public class RefundAcctRepository : IRefundAcctRepository
    {
        private readonly IDbConnectionFactory _connectionFactory;

        public RefundAcctRepository(IDbConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }

        /// <summary>
        /// 根据ID获取退款审核工作记录
        /// </summary>
        public async Task<RefundAcct?> GetByIdAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT 
                    id AS Id,
                    input_date AS InputDate,
                    username AS Username,
                    full_name AS FullName,
                    acct_id AS AcctId,
                    refund_balance_sum AS RefundBalanceSum
                FROM lzfy_refund_acct 
                WHERE id = @Id";
            
            return await connection.QueryFirstOrDefaultAsync<RefundAcct>(sql, new { Id = id });
        }

        /// <summary>
        /// 根据结算ID获取退款审核工作记录
        /// </summary>
        public async Task<RefundAcct?> GetByAcctIdAsync(string acctId)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT 
                    id AS Id,
                    input_date AS InputDate,
                    username AS Username,
                    full_name AS FullName,
                    acct_id AS AcctId,
                    refund_balance_sum AS RefundBalanceSum
                FROM lzfy_refund_acct 
                WHERE acct_id = @AcctId";
            
            return await connection.QueryFirstOrDefaultAsync<RefundAcct>(sql, new { AcctId = acctId });
        }

        /// <summary>
        /// 根据用户名获取退款审核工作记录列表
        /// </summary>
        public async Task<IEnumerable<RefundAcct>> GetByUsernameAsync(string username)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT 
                    id AS Id,
                    input_date AS InputDate,
                    username AS Username,
                    full_name AS FullName,
                    acct_id AS AcctId,
                    refund_balance_sum AS RefundBalanceSum
                FROM lzfy_refund_acct 
                WHERE username = @Username
                ORDER BY input_date DESC";
            
            return await connection.QueryAsync<RefundAcct>(sql, new { Username = username });
        }

        /// <summary>
        /// 获取所有退款审核工作记录
        /// </summary>
        public async Task<IEnumerable<RefundAcct>> GetAllAsync()
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                SELECT 
                    id AS Id,
                    input_date AS InputDate,
                    username AS Username,
                    full_name AS FullName,
                    acct_id AS AcctId,
                    refund_balance_sum AS RefundBalanceSum
                FROM lzfy_refund_acct 
                ORDER BY input_date DESC";
            
            return await connection.QueryAsync<RefundAcct>(sql);
        }

        /// <summary>
        /// 分页获取退款审核工作记录
        /// </summary>
        public async Task<(IEnumerable<RefundAcct> Items, int TotalCount)> GetPagedAsync(
            int pageNumber, 
            int pageSize, 
            string? username = null, 
            string? acctId = null, 
            DateTime? startDate = null, 
            DateTime? endDate = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var whereConditions = new List<string>();
            var parameters = new DynamicParameters();
            
            if (!string.IsNullOrEmpty(username))
            {
                whereConditions.Add("username = @Username");
                parameters.Add("Username", username);
            }
            
            if (!string.IsNullOrEmpty(acctId))
            {
                whereConditions.Add("acct_id = @AcctId");
                parameters.Add("AcctId", acctId);
            }
            
            if (startDate.HasValue)
            {
                whereConditions.Add("input_date >= @StartDate");
                parameters.Add("StartDate", startDate.Value);
            }
            
            if (endDate.HasValue)
            {
                whereConditions.Add("input_date <= @EndDate");
                parameters.Add("EndDate", endDate.Value);
            }
            
            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            
            // 获取总数
            var countSql = $"SELECT COUNT(*) FROM lzfy_refund_acct {whereClause}";
            var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);
            
            // 获取分页数据
            parameters.Add("Offset", (pageNumber - 1) * pageSize);
            parameters.Add("PageSize", pageSize);
            
            var dataSql = $@"
                SELECT 
                    id AS Id,
                    input_date AS InputDate,
                    username AS Username,
                    full_name AS FullName,
                    acct_id AS AcctId,
                    refund_balance_sum AS RefundBalanceSum
                FROM lzfy_refund_acct 
                {whereClause}
                ORDER BY input_date DESC
                OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
            
            var items = await connection.QueryAsync<RefundAcct>(dataSql, parameters);
            
            return (items, totalCount);
        }

        /// <summary>
        /// 创建退款审核工作记录
        /// </summary>
        public async Task<int> CreateAsync(RefundAcct refundAcct)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                INSERT INTO lzfy_refund_acct 
                (username, full_name, acct_id, refund_balance_sum)
                VALUES 
                (@Username, @FullName, @AcctId, @RefundBalanceSum);
                SELECT CAST(SCOPE_IDENTITY() as int);";
            
            // 添加调试日志
            Console.WriteLine($"[DEBUG] CreateAsync - Username: {refundAcct.Username}, FullName: {refundAcct.FullName}, AcctId: {refundAcct.AcctId}");
            
            return await connection.QuerySingleAsync<int>(sql, new
            {
                Username = refundAcct.Username,
                FullName = refundAcct.FullName,
                AcctId = refundAcct.AcctId,
                RefundBalanceSum = refundAcct.RefundBalanceSum
            });
        }

        /// <summary>
        /// 更新退款审核工作记录
        /// </summary>
        public async Task<bool> UpdateAsync(RefundAcct refundAcct)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = @"
                UPDATE lzfy_refund_acct 
                SET 
                    username = @Username,
                    full_name = @FullName,
                    acct_id = @AcctId,
                    refund_balance_sum = @RefundBalanceSum
                WHERE id = @Id";
            
            var rowsAffected = await connection.ExecuteAsync(sql, new
            {
                Id = refundAcct.Id,
                Username = refundAcct.Username,
                FullName = refundAcct.FullName,
                AcctId = refundAcct.AcctId,
                RefundBalanceSum = refundAcct.RefundBalanceSum
            });
            
            return rowsAffected > 0;
        }

        /// <summary>
        /// 删除退款审核工作记录
        /// </summary>
        public async Task<bool> DeleteAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = "DELETE FROM lzfy_refund_acct WHERE id = @Id";
            
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
            return rowsAffected > 0;
        }

        /// <summary>
        /// 检查结算ID是否存在
        /// </summary>
        public async Task<bool> ExistsAsync(string acctId)
        {
            using var connection = _connectionFactory.CreateConnection();
            const string sql = "SELECT COUNT(*) FROM lzfy_refund_acct WHERE acct_id = @AcctId";
            
            var count = await connection.QuerySingleAsync<int>(sql, new { AcctId = acctId });
            return count > 0;
        }

        /// <summary>
        /// 获取用户的退款审核工作统计信息
        /// </summary>
        public async Task<(int TotalCount, decimal TotalAmount)> GetUserStatisticsAsync(
            string username, 
            DateTime? startDate = null, 
            DateTime? endDate = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var whereConditions = new List<string> { "username = @Username" };
            var parameters = new DynamicParameters();
            parameters.Add("Username", username);
            
            if (startDate.HasValue)
            {
                whereConditions.Add("input_date >= @StartDate");
                parameters.Add("StartDate", startDate.Value);
            }
            
            if (endDate.HasValue)
            {
                whereConditions.Add("input_date <= @EndDate");
                parameters.Add("EndDate", endDate.Value);
            }
            
            var whereClause = "WHERE " + string.Join(" AND ", whereConditions);
            
            var sql = $@"
                SELECT 
                    COUNT(*) as TotalCount,
                    ISNULL(SUM(refund_balance_sum), 0) as TotalAmount
                FROM lzfy_refund_acct 
                {whereClause}";
            
            var result = await connection.QuerySingleAsync<dynamic>(sql, parameters);
            
            return ((int)result.TotalCount, (decimal)result.TotalAmount);
        }
    }
}